package cn.com.dhcc.ddi.stat.dao;

import java.util.List;

import org.apache.log4j.Logger;
import org.springframework.stereotype.Repository;

import cn.com.dhcc.app.core.dao.BaseDao;
import cn.com.dhcc.app.core.exception.DaoException;
import cn.com.dhcc.app.core.handler.modulehandler.WhereCondition;
import cn.com.dhcc.app.core.handler.pagerhandler.Pager;
import cn.com.dhcc.app.core.handler.pagerhandler.PagerData;
import cn.com.dhcc.app.core.service.Log;
import cn.com.dhcc.app.pub.CM;
import cn.com.dhcc.app.pub.core.util.dateformatter.DateFormatter;
import cn.com.dhcc.ddi.stat.vo.TaskSyncStatVo;
import cn.com.dhcc.ddi.stat.vo.wrapper.TaskSyncStatRowWrapper;
import cn.com.dhcc.ddi.task.vo.Task;
import cn.com.dhcc.ddi.task.vo.wapper.TaskRowWapper;
/**
 * 
    * @ClassName: TaskSyncStatDao
    * @Description: 任务同步频次统计DAO
    * @author 18315
    * @date 2016-12-16
    *
 */

@Repository
public class TaskSyncStatDao extends BaseDao {
	
	private static Logger logger = Log.getLogger(Log.LOGTYPE.DDI);
	
	/**
	 * 根据任务的每日统计
	 * @return
	 * @throws DaoException
	 */
	public List<TaskSyncStatVo> getDailyTaskStatistics() throws DaoException {
		String sql = "SELECT t5.id AS task_id, t5.statistics_count, t5.statistics_data_size, t6.date_category_id AS cata_id, "
				   + " CASE WHEN t6.data_size IS NULL THEN '0' ELSE t6.data_size END data_size,"
				   + " CASE WHEN t6.total IS NULL THEN '0' ELSE t6.total END total"
				   + " FROM "
				   + "     (SELECT l.id, "
				   + "      GROUP_CONCAT(CASE WHEN success IS NULL THEN '0' ELSE success END ORDER BY hours SEPARATOR '|') statistics_count,"
				   + "      GROUP_CONCAT(CASE WHEN data_size IS NULL THEN '0' ELSE data_size END ORDER BY hours SEPARATOR '|') statistics_data_size"
				   + "      FROM (SELECT t1.id, h.hours FROM "
				   + "         	    (SELECT LPAD(help_topic_id, 2, '0') hours FROM mysql.help_topic WHERE help_topic_id <= 23 ORDER BY help_topic_id) h "
				   + "               RIGHT JOIN "
				   + 		         SI_TABLE("t_ddi_task") + " t1 "
				   + "               ON 1 = 1) l"
				   + "            LEFT JOIN "
				   + " 	             (SELECT t3.task_id, DATE_FORMAT(t3.`LINKENDTIME`, '%H') AS during_time, SUM(t3.total) AS data_size, COUNT(*) AS success FROM "
				   +		          SI_TABLE("t_ddi_task_log") + " t3 "
				   + "                WHERE t3.group_id IS NULL AND DATE(t3.LINKENDTIME) = SUBDATE(CURRENT_DATE(), INTERVAL 1 DAY)"
				   + "                GROUP BY DATE_FORMAT(t3.LINKENDTIME, '%Y-%m-%d %H')) r"
				   + "            ON l.id = r.task_id"
				   + "            AND l.hours = r.during_time"
				   + " 	     GROUP BY id) t5"
				   + " LEFT JOIN "
				   + "      (SELECT t1.task_id, t2.DATE_CATEGORY_ID, SUM(t1.total) AS data_size, COUNT(*) AS total FROM "
				   +              SI_TABLE("t_ddi_task_log") + " t1 "
				   + "       LEFT JOIN "
				   +              SI_TABLE("t_ddi_task") + " t2"
				   + "       ON t1.task_id = t2.id"
				   + "       WHERE t1.group_id IS NULL AND t1.LOG_STATUS = '1' AND DATE(t1.LINKENDTIME) = SUBDATE(CURRENT_DATE(), INTERVAL 1 DAY)"
				   + "       GROUP BY t1.TASK_ID) t6"
				   + " ON t5.id = t6.task_id";	
						   
			    
		
			return CM.getDao().list("按任务获取统计信息", sql, new TaskSyncStatRowWrapper(),new Object[] {});
	}
	
	/**
	 * 根据任务的统计数据查询
	 * @param scope 0-天，1-月，2-年
	 * @return 统计数据
	 * @throws DaoException 
	 */
	public List<TaskSyncStatVo> getTaskStatData(WhereCondition taskParameter,String start, String end) throws DaoException {
		String sql = "SELECT t1.task_id, sum(t1.data_size) AS data_size, sum(t1.total) AS total, t2.task_name FROM "
				   + SI_TABLE("t_ddi_task_sync_statistics") + " t1 "
				   + " LEFT JOIN "
				   + SI_TABLE("t_ddi_task") + " t2 "
				   + " ON t1.task_id = t2.id "
				   + " WHERE t1.stat_date BETWEEN ? AND ? and t2.task_name is not null";

		return CM.getDao().list("按任务获取统计信息", sql, new TaskSyncStatRowWrapper(),taskParameter,new Object[] {start, end});
	}
	
	/**
	 * 分页数据
	 * @param start
	 * @param end
	 * @return
	 * @throws DaoException
	 */
	public PagerData<TaskSyncStatVo> getTaskStatPagerData(Pager pager, WhereCondition condition, String start, String end) throws DaoException {
		String sql = "SELECT t3.*, t4.NAME as cata_name FROM "
				   + "   (SELECT t1.task_id, sum(t1.data_size) AS data_size, sum(t1.total) AS total, t1.stat_date, t2.task_name, t2.DATE_CATEGORY_ID,t2.mp_id FROM "
				   +     SI_TABLE("t_ddi_task_sync_statistics") + " t1 "
				   + "   LEFT JOIN "
				   +     SI_TABLE("t_ddi_task") + " t2 "
				   + "   ON t1.task_id = t2.id "
				   + "   WHERE t1.stat_date BETWEEN ? AND ? "
				   + "   GROUP BY task_id) t3 "
				   + " LEFT JOIN "
				   + SI_TABLE("t_ddi_data_catagory") + " t4 "
				   + " ON t3.DATE_CATEGORY_ID = t4.ID where t3.task_name is not null";
		
		return CM.getDao().getPagerList("按任务获取统计信息", sql, new TaskSyncStatRowWrapper(), pager, condition, new Object[] {start, end});
	}
	
	public List<TaskSyncStatVo> getCalendarData(String start, String end, String taskId,WhereCondition condition) throws DaoException {
		String taskWhere = "";
		Object[] params = new Object[]{};
		
		if(taskId != null) {
			taskWhere = "AND t1.task_id = ?";
			params = new Object[] {start, end, taskId};
		} else {
			params = new Object[] {start, end};
		}
		
		String sql = "SELECT t1.task_id, "
				   + "       t1.stat_date, "
				   + "       SUM(t1.total) AS total, "
				   + "       SUM(t1.data_size) AS data_size FROM "
				   + SI_TABLE("t_ddi_task_sync_statistics") + " t1 "
				   +" LEFT JOIN "+ SI_TABLE("t_ddi_task") +" t2 "
				   +" on t1.task_id= t2.id"
				   + " WHERE t1.stat_date BETWEEN ? AND ?"
				   + taskWhere;

		return CM.getDao().list("获取任务日历数据", sql, new TaskSyncStatRowWrapper(),condition, params);
	}
	
	public PagerData<TaskSyncStatVo> getCalendarPagerData(Pager pager, WhereCondition condition, String start, String end, String taskId) throws DaoException {
		String taskWhere = "";
		Object[] params = new Object[]{start, end};
		if(taskId != null) {
			taskWhere = "AND t1.task_id = ?";
			params = new Object[]{start, end, taskId};
		}
		String sql = "select t1.task_id, t1.stat_date, t1.total, t1.data_size, t2.task_name, t3.name AS cata_name FROM "
	                + SI_TABLE("t_ddi_task_sync_statistics") + " t1 "
	                + " LEFT JOIN "
	                + SI_TABLE("t_ddi_task") + " t2 "
	                + " ON t1.task_id = t2.id "
	                + " LEFT JOIN "
	                + SI_TABLE("t_ddi_data_catagory") + " t3 "			   
	                + " ON t2.DATE_CATEGORY_ID = t3.id "
	                + " WHERE t1.stat_date BETWEEN ? AND ? and t2.task_name is not null "
	                + taskWhere;
		
		return CM.getDao().getPagerList("获取信息列表", sql, new TaskSyncStatRowWrapper(), pager, condition, params);
		
	}
	
	/**
	 * 根据节点获取统计数据
	 * @param type        0-发送 1-接收
	 * @param start       开始时间
	 * @param end         结束时间
	 * @param cataId      数据分类id
	 * @param condition   condition
	 * @return 节点获取统计数据
	 * @throws DaoException
	 */
	public List<TaskSyncStatVo> getNodeStatData(String type, String start, String end, String cataId, WhereCondition condition) throws DaoException {
		Object[] param = new Object[] {};
		String cataWhere = "";
		if(!cataId.equals("all")) {
			cataWhere = "AND t1.cata_id = ? ";
			param = new Object[] {start, end, cataId};
		} else {
			param = new Object[] {start, end};
		}
		
		String sendType = "";
		if(type.equals("0")) {
			sendType = "SRC_DS_ID";
		} else {
			sendType = "DEST_DS_ID";
		}
		
		String sql = "SELECT t5.name AS node_name, t6.name AS cata_name, "
				   + " t3.DATE_CATEGORY_ID AS cata_id, t3." + sendType + " AS node_id, "
				   + " SUM(t4.data_size) AS data_size, SUM(t4.total) AS total, t4.stat_date FROM "
				   + "   (SELECT t1.id, t1.task_name, t1.DATE_CATEGORY_ID, t2." + sendType + " FROM "
				   +         SI_TABLE("t_ddi_task") + " t1 "
				   + "    LEFT JOIN "
				   +         SI_TABLE("t_ddi_ds_mapping") + " t2 "
				   + "    ON t1.MP_ID = t2.ID) t3 "
				   + " LEFT JOIN "
				   +      SI_TABLE("t_ddi_task_sync_statistics") + " t4 "
				   + " ON t3.id = t4.task_id "
				   + " LEFT JOIN "
				   +      SI_TABLE("t_ddi_node_info") + " t5 "
				   + " ON t5.ID = t3." + sendType
				   + " LEFT JOIN "
				   +      SI_TABLE("t_ddi_data_catagory") + " t6 "
				   + " ON t6.ID = t3.DATE_CATEGORY_ID "
				   + " WHERE stat_date BETWEEN ? AND ? " + cataWhere
				   + " GROUP BY node_id, cata_id";
				   
		return CM.getDao().list("获取节点统计数据", sql, new TaskSyncStatRowWrapper(), param);
	}
	
	
	/**
	 * 任务同步列表
	    * @Title: getTaskSyncStatInfo
	    * @Description: TODO(这里用一句话描述这个方法的作用)
	    * @param @return
	    * @param @throws DaoException    参数
	    * @return List<TaskSyncStatVo>    返回类型
	    * @throws
	 */
	public PagerData<TaskSyncStatVo> list(Pager pager, WhereCondition condition) throws DaoException {
		String msg = "";
		String sql = "select DISTINCT t1.*, t2.TASK_NAME taskName, t3.name datacatagoryName FROM "+ SI_TABLE("t_ddi_task_sync_statistics") + " t1"						   
			    + " JOIN " + SI_TABLE("t_ddi_task") + " t2 "
			    + " ON t1.task_id = t2.id"
			    + " JOIN " + SI_TABLE("t_ddi_data_catagory") +" t3 "
			    + " ON t1.cata_id=t3.id"
			    + " where 1=1";
		
		try {
			return CM.getDao().getPagerList("获取信息列表", sql,
					new TaskSyncStatRowWrapper(), pager, condition, new Object[] {});
		} catch (DaoException e) {
			msg = "失败";
			logger.error(msg, e);
			throw new DaoException(msg, e);
		}
	}
	
	
	/**
	 * 获取所有任务信息
	 * @return
	 * @throws DaoException
	 */
	public List<Task> getTasks() throws DaoException {
		String msg = "";
		String sql = "select DISTINCT t1.id, t1.TASK_NAME from "+SI_TABLE("t_ddi_task")+" t1,"
				    + SI_TABLE("t_ddi_task_sync_statistics")+" t2 "
				    + " where t1.id=t2.task_id";
		/*String sql = "select id, TASK_NAME from "+SI_TABLE("t_ddi_task");*/
			    
		try {
			return CM.getDao().list("获取任务信息", sql,
					new TaskRowWapper(),new Object[] {});
		} catch (DaoException e) {
			msg = "获取任务信息失败";
			logger.error(msg, e);
			throw new DaoException(msg, e);
		}
	}
	
	/**
	 * 获取24小时统计
	 * @param id 任务id
	 * @return
	 * @throws DaoException
	 */
	public List<Task> getSynTasksByHour(String id) throws DaoException {
		String msg = "";
		String sql = "SELECT HOUR(t_ddi_task_log.MTIME) AS hour, t_ddi_task.id, SUM(t_ddi_task_log.total) AS sum FROM "
				     + SI_TABLE("t_ddi_task") + " RIGHT JOIN " + SI_TABLE("t_ddi_task_log") 
				     + " ON (t_ddi_task.`id` = t_ddi_task_log.`task_id`)"
				     + " WHERE t_ddi_task.id = ? AND t_ddi_task_log.MTIME LIKE ? AND t_ddi_task_log.log_status=1 AND t_ddi_task_log.LINKTYPE=1"
				     + " GROUP BY DATE_FORMAT(t_ddi_task_log.MTIME, '%Y-%m-%d %H ')";
		
		String date = DateFormatter.getToday("yyyy-MM-dd") + "%";
		
		try {
			return CM.getDao().list("获取24小时同步统计", sql, new TaskRowWapper(), new Object[] {id, date});
		} catch (DaoException e) {
			msg = "数据源失败";
			logger.error(msg, e);
			throw new DaoException(msg, e);
		}
	}
	public List<Task> getSynTotalByHour(String id) throws DaoException {
		String msg = "";
		String sql = "SELECT HOUR(t_ddi_task_log.MTIME) AS hour, t_ddi_task.id, count(t_ddi_task_log.id) AS count FROM "
				     + SI_TABLE("t_ddi_task") + " RIGHT JOIN " + SI_TABLE("t_ddi_task_log") 
				     + " ON (t_ddi_task.`id` = t_ddi_task_log.`task_id`)"
				     + " WHERE t_ddi_task.id = ? AND t_ddi_task_log.MTIME LIKE ? AND t_ddi_task_log.log_status=1 AND t_ddi_task_log.LINKTYPE=1"
				     + " GROUP BY DATE_FORMAT(t_ddi_task_log.MTIME, '%Y-%m-%d %H ')";
		
		String date = DateFormatter.getToday("yyyy-MM-dd") + "%";
		
		try {
			return CM.getDao().list("获取24小时同步统计", sql, new TaskRowWapper(), new Object[] {id, date});
		} catch (DaoException e) {
			msg = "数据源失败";
			logger.error(msg, e);
			throw new DaoException(msg, e);
		}
	}
	
	/**
	 * 获取指定时间段内的数据量/频次统计数据
	 * @param start 开始日期
	 * @param end	结束日期
	 * @return	TaskSyncStatVo列表
	 * @throws DaoException
	 */
	public List<TaskSyncStatVo> getTotalInfo(String start, String end) throws DaoException {
		String msg = "";
		String sql = "select task_id, total,SUM(total) AS num, data_size, SUM(data_size) AS sum FROM " 
					  + SI_TABLE("t_ddi_task_sync_statistics") 
					  + " WHERE stat_date BETWEEN ? AND ?" 
					  + "GROUP BY task_id";
		try {
			return CM.getDao().list("同步数据信息", sql, new TaskSyncStatRowWrapper(), new Object[] {start, end});
		} catch (DaoException e) {
			msg = "每天同步数据统计失败";
			logger.error(msg, e);
			throw new DaoException(msg, e);
		}
	}
	
	
	/**
	 * 计算每日统计总量
	 * @return 
	 * @throws DaoException
	 */
	public List<TaskSyncStatVo> CalculateDailySynStatistics() throws DaoException {
		String msg = "";
		String sql = "SELECT  t1.task_id,t2.date_category_id as cata_id,COUNT(t1.id) AS sum,SUM(t1.total) AS num"
				+ " FROM " +SI_TABLE("t_ddi_task_log")+" t1 "
				+ " INNER JOIN " + SI_TABLE("t_ddi_task")+" t2 "
				+ " ON (t1.task_id=t2.id)"
				+ " LEFT JOIN "+SI_TABLE("t_ddi_data_catagory")+" t3 "
				+ " ON (t2.date_category_id=t3.id)"
				+ " WHERE date_format(t1.mtime,'%Y-%m-%d %H ') like ? " 
				+ " AND t1.LOG_STATUS=1"
				+ " AND t1.LINKTYPE= 1"
				+ " GROUP BY t1.task_id";
		
		String date = DateFormatter.getDateBasedOnToday("yyyy-MM-dd", -1) + "%";
		try {
			return CM.getDao().list("获取每日数据统计", sql, new TaskSyncStatRowWrapper(), new Object[] {date});
		} catch (DaoException e) {
			msg = "每天数据统计失败";
			logger.error(msg, e);
			throw new DaoException(msg, e);
		}
	}
	
	/**
     * 新增
     * @param obj
     * @return
     * @throws DaoException
     */
    public int insert(TaskSyncStatVo obj) throws DaoException {
        return getDao().insertModule("新增",obj);
    }
	
    /**
     * 修改
     * @param obj
     * @return
     * @throws DaoException
     */
    public int update(TaskSyncStatVo obj) throws DaoException {
        return getDao().updateModuleByIdExecuteNull("修改",obj);
    }
    


}
